﻿using HostAssistantCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.ServiceProcess;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace HostAssistantBLL
{
    /// <summary>
    /// 数据库列表
    /// </summary>
    public class dbbll
    {
        #region 刷新列表
        /// <summary>
        /// 刷新列表
        /// </summary>
        public static void Reshmydblist()
        {

            //mysql

       
            //获取存在的数据库路径
            string password = "";
            string port = "";
            var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
            var mysql = set.FirstOrDefault(o => o.type == "mysql");
            if (mysql != null)
            {
                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";
                //连接数据库
                MySqlDbHelper mdb = new MySqlDbHelper();
                mdb.Initialize(ip, "mysql", user, password, port);


                if (mdb.ConnectionTest()==null)
                    {
                    string datapath = mysql.MyPath;

                    // 获取数据库信息
                     
                     MySqlManger.GetMySqlInfos( ip,  user, password, port, datapath);
                    //{

                    //    throw new Exception("未能获取到数据库信息!");



                    //}

                }
                else
                    {

                    throw new Exception("未能连接上数据库,请检查数据库账号密码!");
                  
                    }

                }
                else
                {
                throw new Exception("首次使用请先配置数据库");
            }

          



        }

        public static void Reshmsdblist()
        {
            //获取基本配置信息
            string password = "";
            string port = "";
            var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
            var mysql = set.FirstOrDefault(o => o.type == "mssql");
            if (mysql != null)
            {
                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";
                //连接数据库
                SqlDbHelper.Initialize("master", user, password);
                    if (SqlDbHelper.ConnectionTest())
                    {
                        
                        SqlServerManager.GetMySqlInfos(password,user);
                       

                }
                    else
                    {
                       
                    throw new Exception("未能连接上数据库,请检查数据库账号密码!");


                }

            }
                else
                {
                    

                throw new Exception("首次使用请先配置mssql数据库");


            }




        }



        #endregion

        #region 删除数据库
        public static void delete(string id)
        {
            SqlInfo s = GetSqlInfo(id);
            if (s.type == "mysql")
            {
                dmysql(s);

            }
            else
            {
                dmssql(s);
            }








        }
        static void dmssql(SqlInfo s)
        {
            //获取存在的数据库路径
            string password = "";
            string port = "";
            var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
            var mysql = set.FirstOrDefault(o => o.type == "mssql");
            if (mysql != null)
            {

                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";

                SqlDbHelper.Initialize("master", user, password);

                // 连接数据库

                if (SqlDbHelper.ConnectionTest())
                {

                    // 删除数据库        
                    if (!SqlDbHelper.ExecuteNormalSql("drop database " + s.Mdbname))
                    {


                        throw new Exception("未能连接上数据库,删除数据库失败");


                    }


                    // 删除数据库数据


                    DeleteSqlInfo(s.ID.ToString());


                    //删除数据库用户
                    SqlDbHelper.ExecuteNormalSql("drop login " + s.Mdbname);
                    //   DbHelperSQL.ExecuteNormalSql("drop user " + s.Mdbname);


                    //成功删除数据库


                }
                else
                {


                    throw new Exception("未能连接上数据库,请检查数据库账号密码");

                }

            }
            else
            {

                throw new Exception("首次使用请先配置数据库");
            }



        }
        static void dmysql(SqlInfo s)
        {
            //获取存在的数据库路径
            string password = "";
            string port = "";
            var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
            var mysql = set.FirstOrDefault(o => o.type == "mysql");
            if (mysql != null)
            {
                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";
                //连接数据库
                MySqlDbHelper mdb = new MySqlDbHelper();
                mdb.Initialize(ip, "mysql", user, password, port);
                if (mdb.ConnectionTest() == null)
                {
                    // 删除数据库
                    if (!mdb.Touch("drop database " + s.Mdbname))
                    {
                        throw new Exception("删除数据库失败");
                    }

                    //删除数据库数据

                    DeleteSqlInfo(s.ID.ToString());

                    //删除数据库用户
                    mdb.Update("Delete FROM mysql.user Where user='" + s.Mdbname + "' and Host='%'");
                    mdb.Update("flush privileges");
                    //成功删除数据库
                }
                else
                {
                    throw new Exception("未能连接上数据库,请检查数据库账号密码");

                }

            }
            else
            {
                throw new Exception("首次使用请先配置数据库");
            }
        }





        #region  获取 数据库
        public static void DeleteSqlInfo(string id)
        {
            string sql = "    id=@ID  ";
            SqlInfo SqlInfo = new SqlInfo();
            SqlInfo.ID = ZConvert.StrToInt(id);
            Data<SqlInfo>.GetInstance().Delete("SqlInfo", sql, SqlInfo);
        }
        #endregion

        #endregion

        #region 数据库列表
        public static List<SqlInfo> GetsiteList(string Mdbname, string type)
        {
            SqlInfo SqlInfo = new SqlInfo();
            string sql = "  select * from SqlInfo where   1=1   ";
            List<SqlInfo> list = new List<SqlInfo>();
            if (!string.IsNullOrWhiteSpace(Mdbname))
            {
                SqlInfo.Mdbname ="%"+ Mdbname+ "%";
                sql += "  and Mdbname like @Mdbname  ";
            }
            if (!string.IsNullOrWhiteSpace(type))
            {
                SqlInfo.type = type;
                sql += "  and type=@type  ";
            }
          
         
            
            list = Data<SqlInfo>.GetInstance().GetModelList(sql, SqlInfo);
            return list;
        }
        #endregion

        #region  获取 数据库
        public static SqlInfo GetSqlInfo(string id)
        {
            string sql = "    id=@ID  ";
            SqlInfo SqlInfo = new SqlInfo();
            SqlInfo.ID = ZConvert.StrToInt(id);
            return Data<SqlInfo>.GetInstance().GetModel("SqlInfo", sql, SqlInfo);
        }
        #endregion

        #region mysql备份

        public static string EcreateTaskSchd_Mysql_Single(string bakname, string smdbname, string savepath)
        {
            string errorCode = null;
            try
            {
                if (!System.IO.Directory.Exists(savepath))
                {
                    System.IO.Directory.CreateDirectory(savepath);
                }
                //string insert_path_sql = "update yroot_mysql set mspath=? where id=1";
                //string[] pargs = new string[] { savepath };
                //string[] pfield = new string[] { "mspath" };
                //yroot.ADO.IISAdoHelper.adoNormalExecute(insert_path_sql, pargs, pfield);


                string password = "";
                string port = "";
                var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
                var mysql = set.FirstOrDefault(o => o.type == "mysql");

                string path = mysql?.Path ?? "";
                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";
                if (!System.IO.File.Exists(path + "\\bin\\mysqldump.exe"))
                {
                    errorCode = "不存在mysql备份工具";
                    return errorCode;
                }
                //string root = System.Environment.CurrentDirectory;
                //string defaultinifile = root + "\\conf\\default.ini";
                //string datapath = yroot.Tools.INIOperationClass.INIGetStringValue(defaultinifile, "sys", "mysqldatapath", "");
                //if (datapath == "") { datapath = path + @"\data"; }
                //  string sdatapath = @"E:\bbbbbbbbbbbbbbbbbbbbbbb" + "\\" + smdbname;
                MySqlDbHelper mdb = new MySqlDbHelper();
                mdb.Initialize(ip, "mysql", user, password, port);
                DataSet ds_mysql_dbname = mdb.GetDataSet(CommandType.Text, "show databases", null);
                bool mysql_exist_flag = false;
                if (ds_mysql_dbname.Tables[0].Rows.Count != 0)
                {
                    /*
                     * 遍历数据
                     */
                    DataTable dt_dbnames = ds_mysql_dbname.Tables[0];
                    //遍历行
                    foreach (DataRow dr in dt_dbnames.Rows)
                    {
                        if (dr["database"].ToString() == smdbname)
                        {
                            mysql_exist_flag = true;
                        }
                    }
                }
                if (mysql_exist_flag)//存在继续执行
                {

                    string batstr = path + "\\bin\\mysqldump --opt -u root --password=" + password + " --default-character-set=utf8 " + smdbname + " > " + savepath + "\\" + bakname;// + smdbname + "_"+ DateTime.Now.ToString("yyyyMMddhhmmss") + ".sql";
                    Cmd.cmddos(batstr);



                    return errorCode;
                }
                else
                {
                    errorCode = "不存在指定数据库";
                    return errorCode;
                }
            }
            catch (Exception ex)
            {
                errorCode = ex.Message;
                NLogger.Error("mysql备份:" + ex.ToString());
                return errorCode;
            }

        }

        #endregion

        #region mysql还原

        public static string RebackupSql(string mpath, string port, string ip, string user, string password, bool beforebackup, string sqlpath, string db_backup_name)
        {
            string errorCode = null;


            NLogger.Info("正在恢复MySQL数据库文件:" + db_backup_name);
            ////恢复前备份
            //if (beforebackup)
            //{
            //   NLogger.Info("恢复前备份:" + db_backup_name);

            //    string before_backup_path = sqlpath.Substring(0, sqlpath.LastIndexOf("\\")) + "\\before_backup";

            //NLogger.Info("恢复前备份:" + db_backup_name+"--:"+ before_backup_path);
            ////创建备份目录
            //if (!Directory.Exists(before_backup_path))
            //    {
            //        try
            //        {
            //            Directory.CreateDirectory(before_backup_path);
            //        }
            //        catch
            //        {
            //            before_backup_path = "";
            //        }
            //    }
            //    if (before_backup_path != "" && db_backup_name != "backupOfalldatabases")//备份数据库
            //    {
            //        try
            //        {
            //            /*
            //            * 生成bat命令
            //            */
            //            string backuptime = DateTime.Now.ToString("yyyyMMddhh");
            //            string batstr = "\"" + mpath + "\\bin\\mysqldump\" --opt -u " + dbname + " --password=" + dbpass + " --default-character-set=utf8 " + db_backup_name + " > " + before_backup_path + "\\" + db_backup_name + "_beforebackup_" + backuptime + ".sql";



            //        yroot.Tools.DosUtil.Execute("net start mysql");//启动服务
            //            string output = yroot.Tools.DosUtil.Execute(batstr);//执行命令
            //            //判断是否备份成功
            //            if (!System.IO.File.Exists(before_backup_path + "\\" + db_backup_name + "_beforebackup_" + backuptime + ".sql"))
            //            {
            //                cslog.Logger.Log("数据库 " + db_backup_name + "备份失败", cslog.ErrorLevel.Info, this.GetType().FullName);
            //            }
            //            else
            //            {
            //                cslog.Logger.Log("数据库 " + db_backup_name + "备份成功", cslog.ErrorLevel.Info, this.GetType().FullName);
            //            }
            //        }
            //        catch
            //        {
            //            cslog.Logger.Log("数据库 " + db_backup_name + "备份失败", cslog.ErrorLevel.Info, this.GetType().FullName);
            //        }
            //    }
            //}



            //恢复单个数据库
            try
            {

                /*
                * 生成bat命令
                */
                //  string backuptime = DateTime.Now.ToString("yyyyMMddhh");
                string dbcreate_str = "\"" + mpath + "\\bin\\mysqladmin\" -u " + user + " -p" + password + " create " + db_backup_name;
                //string batstr = "\"" + mpath + "\\bin\\mysql\" -u" + dbname + " -p" + dbpass + " " + db_backup_name + "<\"" + sqlpath + "\"";
                string xpath = mpath + @"\bin\mysql";
                string batstr = "-u " + user + " -p " + password + " " + db_backup_name + "<\"" + sqlpath + "\"";
                //  Cmd.cmddos("net start mysql");//启动服务
                Cmd.cmddos(dbcreate_str);//执行命令
                                         //生成bat文件
                                         //  string root = System.Environment.CurrentDirectory;
                                         //  string bat_file_path = root + "\\data\\" + db_backup_name + "_backup_tmp.bat";
                                         //  System.IO.File.WriteAllText(bat_file_path, xpath + " " + batstr, Encoding.Default);
                                         //  yroot.Tools.DosUtil.ExecuteFileName(xpath, batstr);//执行命令

                Cmd.cmddos(xpath + " " + batstr);

                try
                {
                    //查看数据库是否存在表 判断结果成功与否
                    MySqlDbHelper mdb = new MySqlDbHelper();
                    mdb.Initialize(ip, db_backup_name, user, password, port);
                    DataSet ds_mysql_dbname = mdb.GetDataSet(CommandType.Text, "show tables", null);
                    if (ds_mysql_dbname.Tables[0].Rows.Count == 0)
                    {
                        //无法还原数据库时 只能使用默认命令还原 太固定容易出问题
                        string solid_batstr = "mysql -u" + user + " -p" + password + " " + db_backup_name + "<\"" + sqlpath + "\"";
                        //string solid_batstr = "mysqldump -u" + dbname + " -p" + dbpass + " –all-databases < \"" + sqlpath + "\"";
                        Cmd.cmddos(solid_batstr);//执行命令
                                                 //DataSet solid_ds_mysql_dbname = mdb.GetDataSet(CommandType.Text, "show tables", null);
                                                 //暂时不做判断
                                                 //if (solid_ds_mysql_dbname.Tables[0].Rows. Count == 0)
                                                 //{
                                                 //    errorCode = ErrorCode.CommitFail;
                                                 //}
                    }
                    ///*---- 删除替代数据库 ----*/
                    //if (dbtype == "mysql" && db_backup_name == "backupOfalldatabases")
                    //{
                    //    /*---- mdb ----*/
                    //    try
                    //    {
                    //        mdb.Initialize("localhost", "mysql", "root", dbpass, mport);
                    //        mdb.Update("drop database " + "backupOfalldatabases");
                    //    }
                    //    catch
                    //    {

                    //    }
                    //}


                }
                catch (Exception ex)
                {

                    NLogger.Error("还原数据库：" + ex.ToString());
                    errorCode = ex.Message;
                    return errorCode;
                }



            }
            catch (Exception ex)
            {

                NLogger.Error("还原数据库：" + ex.ToString());
                errorCode = ex.Message;
                return errorCode;
            }



            return errorCode;
        }

        #endregion

        #region mssql备份

        public static string EcreateTaskSchd_Mssql_Single(string bakname, string smdbname, string savepath)
        {
            string errorCode = null;
            try
            {
                if (!System.IO.Directory.Exists(savepath))
                {
                    System.IO.Directory.CreateDirectory(savepath);
                }


                string password = "";
                string port = "";
                var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
                var mysql = set.FirstOrDefault(o => o.type == "mssql");
                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";

                SqlDbHelper.Initialize("master", user, password);
                SqlDbHelper.ExecuteNormalSql("BACKUP DATABASE [" + smdbname + "] TO  DISK = N'" + savepath + "\\" + bakname + "' WITH NOFORMAT, NOINIT,  NAME = N'" + smdbname + "-完整 数据库 备份', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 ");

                errorCode = "备份完成";

                return errorCode;

            }
            catch (Exception ex)
            {
                errorCode = ex.Message;
                NLogger.Error("mssql备份:" + ex.ToString());
                return errorCode;
            }

        }

        #endregion

        #region mssql还原

        public static string RebackupmsSql(string savepath, string smdbname)
        {


            string errorCode = null;
            try
            {


                string password = "";
                string port = "";
                var set = Data<HostAssistantCore.setting>.GetInstance().GetModelList("select *  from setting", null);
                var mysql = set.FirstOrDefault(o => o.type == "mssql");
                string ip = mysql?.ip ?? "";
                port = mysql?.port ?? "";
                string user = mysql?.username ?? "";
                password = mysql?.userpwd ?? "";

                SqlDbHelper.Initialize("master", user, password);

                NLogger.Info("11");
                SqlDbHelper.ExecuteNormalSql(" RESTORE DATABASE [" + smdbname + "] FROM  DISK = N'" + savepath + "' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10  ");


                SqlDbHelper.ExecuteNormalSql("select  1");


                NLogger.Info("22");



            }
            catch (Exception ex)
            {
                errorCode = ex.Message;
                NLogger.Error("mssql还原:" + ex.ToString());

            }
            finally
            {
                NLogger.Info("222222222222");

            }
            return errorCode;
        }

        #endregion

    }
}
